package ejb.Service;
import java.util.List;
import java.math.BigInteger;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import ejb.Entity.TweetWithGeo;

import static java.lang.System.out;
/**
 * This EJB session bean has two methods to implement searching functions
 * @author Yu Qiao, Wei Chen and Igor
 *
 */

@Stateless
public class SearchSessionBean {
	/**
	 * The name of the persistence unit as defined in the persistence.xml file.
	 */
	@PersistenceContext(unitName="examples-769-EJB")
	/**
	 * Entity manager.
	 */
	EntityManager em;
	/**
	 * get tweets from H2 database through the entity manager in JPA. 
	 * @param keyword a list of keywords.
	 * @param location a list of locations.
	 * @param option an indicator whether the query is to return count or actual tweets.
	 * @return
	 */
	public Integer getTweetFromH2(String [] keyword, String [] location, int option){
		String query=constructQuery(keyword,location,1);
		return ((BigInteger)em.createNativeQuery(query).getSingleResult()).intValue();
	}
	/**
	 * @param keyword a list of keywords.
	 * @param location a list of locations.
	 * @return a list of Tweets.
	 */
	public List<TweetWithGeo> getTweetFromH2(String [] keyword, String [] location){
		String query=constructQuery(keyword,location,2);
		query+=" limit 400";
		return em.createNativeQuery(query,ejb.Entity.TweetWithGeo.class).getResultList();
	}
	/**
	 * Construct a native sql query based on user input parameters and option.
	 * @param keyword a list of keywords.
	 * @param location a list of locations.
	 * @param option whether return count or tweets, 1 for count, 2 for actual tweets.
	 * @return
	 */
	public static String constructQuery(String [] keyword, String [] location, int option){
		String query=null;
		String inClauseLocation = null;
		String regExpClause = null;
		if(option==1){
			query="SELECT count(*) FROM TWEET_GEO_VIEW ";
		}else if(option==2){
			query="SELECT * FROM TWEET_GEO_VIEW ";
		}
		if (location != null){
			inClauseLocation = " ('"+location[0]+"'";
			if(location.length>0){
				int i;
				for(i=1;i<location.length;i++){
					inClauseLocation += "," + "'" + location[i] + "'";
				}
			}			
			inClauseLocation += ") ";			
			
			
		}

		if(keyword != null)
		{
			regExpClause = "UPPER(text) "+sqlRegexp(keyword[0]);
			if(keyword.length>0){
				int i;
				for(i=1;i<keyword.length;i++){
					regExpClause += " OR UPPER(text) " + sqlRegexp(keyword[i]);
				}
			}					
		}
		//only use location to search
		if(regExpClause == null){
			query += "where UPPER(stusps) in" + inClauseLocation + "or UPPER(stname) in" + inClauseLocation;
		}
		//only use keyword to search 
		else if (inClauseLocation == null){
			query += "where " + regExpClause;
		}
		//use both keyword and location to search
		else{
			query += "where (UPPER(stusps) in" + inClauseLocation + "or UPPER(stname) in" + inClauseLocation
					+ ") and (" + regExpClause + ")"; 
		}
		return query;
	}
	/**
	 * A utility function to construct a regular expression of where constrain.
	 * @param s a string to be converted to regular expression.
	 * @return a regexp string.
	 */
	public static String sqlRegexp(String s){
		return "REGEXP '[^A-Z]"+s+"[^A-Z]'";
	}

}
